package net.wantongtech.xa.uploadbasicinfo.dao;

import com.wtkj.constant.NewClearingConstant;
import com.wtkj.dao.impl.BaseDataTemplate;
import com.wtkj.vo.dzzf.base.qxsjz.entity.MergeTransLog;
import com.wtkj.vo.dzzf.base.qxsjz.entity.PortalTransOut;
import com.wtkj.zwxt.stat.CardStaticValue;
import com.wtkj.zwxt.xxjh.share.state.TradeStaticValue;
import org.javatuples.Pair;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.support.JdbcUtils;
import org.springframework.stereotype.Repository;

import java.util.List;

/**
 * 修复待合并原始交易车牌号服务实现
 *
 * @author MaShibo
 * 日期 :  2020/2/20
 */
@Repository
public class CorrectMergeTransVehPlateDao extends BaseDataTemplate {

    public List<String> queryErrorMergePassId() {
        final String sql = "SELECT a.passid from tr_merge_log a";
        return jdbcTemplate.queryForList(sql, String.class);
    }

    public List<MergeTransLog> queryErrorMergeLog() {
        final String sql =
                "SELECT *\n" +
                        "  FROM tr_merge_log a\n" +
                        " WHERE a.error LIKE '%车牌号%'\n" +
                        "   AND a.cardno <> '61010000000000000000'";
        return jdbcTemplate.query(sql, BeanPropertyRowMapper.newInstance(MergeTransLog.class));
    }


    /**
     * 根据passId查询待合并原始交易对应的transactionId - 车牌号
     *
     * @param passId 通行记录ID
     * @return transactionId - 车牌号
     */
    public List<PortalTransOut> queryTransPairByPassId(String passId) {
        final String sql =
                "SELECT tr.transactionid, tr.vehicleplate, tr.vehiclecolor, tr.etccardid\n" +
                        "  FROM tr_portal_transout tr\n" +
                        " WHERE tr.servicetype = ?\n" +
                        "   AND tr.isintegrated = ?\n" +
                        "   AND tr.state IN (?, ?)\n" +
                        "   AND tr.passid = ?";
        return jdbcTemplate.query(sql, BeanPropertyRowMapper.newInstance(PortalTransOut.class),
                NewClearingConstant.ServiceType.ETC.getValue(),
                NewClearingConstant.IntegratedState.NONE, TradeStaticValue.TradeState.ACCOUNT,
                TradeStaticValue.TradeState.DSPT_ACCOUNT, passId);
    }


    public List<PortalTransOut> queryPortalTransOutByMergeTransLog(MergeTransLog mergeTransLog) {
        final String sql =
                "SELECT tr.transactionid, tr.vehicleplate, tr.vehiclecolor, tr.etccardid\n" +
                        "  FROM tr_portal_transout tr\n" +
                        " WHERE tr.servicetype = ?\n" +
                        "   AND tr.isintegrated = ?\n" +
                        "   AND tr.state IN (?, ?)\n" +
                        "   AND tr.passid = ?\n" +
                        "   AND tr.etccardid = ?\n" +
                        "   AND tr.tollprovinceid = ?";
        return jdbcTemplate.query(sql, BeanPropertyRowMapper.newInstance(PortalTransOut.class),
                NewClearingConstant.ServiceType.ETC.getValue(),
                NewClearingConstant.IntegratedState.NONE, TradeStaticValue.TradeState.ACCOUNT,
                TradeStaticValue.TradeState.DSPT_ACCOUNT, mergeTransLog.getPassId(),
                mergeTransLog.getCardNo(), mergeTransLog.getTollProvinceId());
    }


    public Pair<String, Integer> queryVehPlateByECardNo(String eCardNo) {
        final String sql =
                "SELECT vehicleplate, vehiclecolor\n" +
                        "  FROM (SELECT a.vehicleplate, a.vehiclecolor, a.opton\n" +
                        "          FROM ac_cardinfo a\n" +
                        "          LEFT JOIN ac_account t\n" +
                        "            ON a.accountid = t.accountid\n" +
                        "          LEFT JOIN ac_fundaccount f\n" +
                        "            ON t.fundaccountid = f.fundaccountid\n" +
                        "         WHERE a.ecardno = ?\n" +
                        "           AND a.ecardstatus <> ?\n" +
                        "        UNION ALL\n" +
                        "        SELECT a.vehicleplate, a.vehiclecolor, a.opton\n" +
                        "          FROM ac_cardinfo a\n" +
                        "          JOIN ac_cardveh v\n" +
                        "            ON a.cardinfoid = v.cardinfoid\n" +
                        "          LEFT JOIN ac_account t\n" +
                        "            ON a.accountid = t.accountid\n" +
                        "          LEFT JOIN ac_fundaccount f\n" +
                        "            ON t.fundaccountid = f.fundaccountid\n" +
                        "         WHERE v.ecardno = ?)\n" +
                        " ORDER BY opton DESC\n";
        return jdbcTemplate.query(sql, rs -> {
            if (rs.next()) {
                return Pair.with(rs.getString(1),
                        ((Integer) JdbcUtils.getResultSetValue(rs, 2, Integer.class)));
            }
            return null;
        }, eCardNo, CardStaticValue.EcardStatus.EcardState_SQWQR, eCardNo);
    }


    public int updateTransVehPlate(String transactionId, String vehPlate) {
        final String sql =
                "UPDATE tr_portal_transout tr\n" +
                        "   SET tr.vehicleplate = ?\n" +
                        " WHERE tr.transactionid = ?";
        return jdbcTemplate.update(sql, vehPlate, transactionId);
    }

    public int updateTransVehColor(String transactionId, Integer vehColor) {
        final String sql =
                "UPDATE tr_portal_transout tr\n" +
                        "   SET tr.vehiclecolor = ?\n" +
                        " WHERE tr.transactionid = ?";
        return jdbcTemplate.update(sql, vehColor, transactionId);
    }

    public void deleteMergeLog(String passId) {
        final String sql = "DELETE FROM tr_merge_log a WHERE a.passid = ?";
        jdbcTemplate.update(sql, passId);
    }
}
